package com.sophossystems.client.asmon.dao.spring;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.transaction.annotation.Transactional;

import com.sophossystems.client.asmon.dao.RecolectaDocDao;
import com.sophossystems.client.asmon.dto.RecolectaDocDTO;
import com.sophossystems.client.asmon.dto.RecolectaDocPkDTO;
import com.sophossystems.client.asmon.exceptions.DaoException;
import com.sophossystems.client.asmon.hibernate.RecolectaDoc;

public class RecolectaDocDaoImpl extends AbstractDAO implements  RecolectaDocDao{
	
	
	private static Logger log = Logger.getLogger(RecolectaDocDaoImpl.class);
	
	@Override
	public RecolectaDocPkDTO insert(final RecolectaDocDTO dto) {
		
		RecolectaDocPkDTO exito =null ;
		try {
			 exito = (RecolectaDocPkDTO) getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
				    Transaction tx = session.beginTransaction();
				    RecolectaDocPkDTO pk = null;
				    RecolectaDoc recolecta = new RecolectaDoc();
				    recolecta.mapper(dto);
				    session.save(recolecta);
				    tx.commit();
				   
				    try{
				    	RecolectaDocPkDTO res = findLastInsert();
				    	System.out.println("PK encontrada " + res.getIdRecolectaDoc());
				    	pk = new RecolectaDocPkDTO();
				    	pk.setIdRecolectaDoc(res.getIdRecolectaDoc());
				    }catch (DaoException es) {
				
				    	throw new HibernateException("Insersion: " +es.getMessage());
					}
					return pk;
				
					
				}

				
			});
		///	 return exito;
		} catch (final Exception e) {
			logger.error("Error: getRecolectaDocDao, cause: " + e.getMessage(), e);
			
		}
		return exito;

	}

	@Override
	public void update(final RecolectaDocDTO dto)
			throws DaoException {
		
		try {
			 getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					RecolectaDocPkDTO pk = null;
					RecolectaDocDTO res = null;
					Transaction tx = session.beginTransaction();
					// Aqui primero se busca si existe el registro
					try {
						res = findByPrimaryKey(dto
								.getIdRecolectaDoc());
						pk = new RecolectaDocPkDTO();
						pk.setIdRecolectaDoc(res.getIdRecolectaDoc());
					} catch (DaoException es) {
						throw new HibernateException(
								"Update RecolectaDocDao: "
										+ es.getMessage());
					}
					///aqui se actualizar los campos y con los de la entrada  
					//cuidado de actualiza todo.
					RecolectaDoc persis = new RecolectaDoc();
					persis.mapper(dto);
					persis.setIdRecolectaDoc(res.getIdRecolectaDoc());
					session.update(persis);
					tx.commit();
					return null;
				}		
			});
		} catch (final Exception e) {
			logger.error("Error: update recoleccion Documentos. , cause: " + e.getMessage(), e);
			throw new DaoException("Error: update recoleccion Documentos., cause: " + e.getMessage());
		}
		
		
	}

	@Override
	public void delete(final RecolectaDocPkDTO pk) throws DaoException {
	
		try {
			 getHibernateTemplate().execute(new HibernateCallback() {
				/**
				 * {@inheritDoc}
				 * 
				 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
				 */
				@Override
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
				    Transaction tx = session.beginTransaction();
				    RecolectaDoc recolecta = (RecolectaDoc)session.load(RecolectaDoc.class,pk.getIdRecolectaDoc() );
				    session.delete(recolecta);
					tx.commit();
					return null;
				}		
			});
		} catch (final Exception e) {
			logger.error("Error: getActivity, cause: " + e.getMessage(), e);
			throw new DaoException("Error: getActivity, cause: " + e.getMessage());
		}
		
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "RecolectaDoc";
	}

	/** 
	 * Returns all rows from the RECOLECTA_DOC table that match the criteria 'idRecolectaDoc = :idRecolectaDoc'.
	 */
	@Override
	@Transactional
	public RecolectaDocDTO findByPrimaryKey(final int idRecolectaDoc)
			throws DaoException	{
		RecolectaDocDTO recolecta = null;
		try {
			recolecta = (RecolectaDocDTO)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@SuppressWarnings("unchecked")
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						
							RecolectaDocDTO recolecta = null;
							Query oQuery = session.createQuery("SELECT rec.idRecolectaDoc as idRecolectaDoc, rec.idCliente as idCliente," +
									" rec.idDireccion as idDireccion, rec.contacto as contacto, rec.tel1 as tel1, rec.ext1 as ext1, rec.periodoRec as periodoRec," +
									" rec.horarioRecoleccion as horarioRecoleccion, rec.zona as zona,rec.lugarRecoleccion as lugarRecoleccion FROM " + getTableName() + 
									" rec WHERE rec.idRecolectaDoc = :idRecolecta");
							oQuery.setInteger("idRecolecta", idRecolectaDoc);
							List<RecolectaDocDTO> lista = oQuery.setResultTransformer(
									Transformers.aliasToBean(RecolectaDocDTO.class)).list();
							if(lista != null && lista.size() > 0){
								recolecta = lista.get(0);
								return recolecta;
							}else{
								
							}
							return recolecta;
						}
					});
//			List<RecolectaDocDTO> list = jdbcTemplate.query("SELECT idRecolectaDoc, idCliente, idDireccion, contacto, tel1, ext1, periodoRec, horarioRecoleccion, zona FROM " + getTableName() + " WHERE idRecolectaDoc = ?", this,idRecolectaDoc);
//			return list.size() == 0 ? null : list.get(0);
		}
		catch (Exception e) {
			throw new DaoException("Query failed", e);
		}
		return recolecta;
	}

	/** 
	 * Returns all rows from the RECOLECTA_DOC table that match the criteria ''.
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional
	public List<RecolectaDocDTO> findAll() 
			throws DaoException	{
		List<RecolectaDocDTO> recolecta = null;
		try {
			recolecta = (List<RecolectaDocDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
			
							Query oQuery = session.createQuery("SELECT rec.idRecolectaDoc as idRecolectaDoc, rec.idCliente as idCliente," +
									" rec.idDireccion as idDireccion, rec.contacto as contacto, rec.tel1 as tel1, rec.ext1 as ext1, rec.periodoRec as periodoRec," +
									" rec.horarioRecoleccion as horarioRecoleccion, rec.zona as zona, rec.lugarRecoleccion as lugarRecoleccion FROM " + getTableName() + " rec ORDER BY rec.idRecolectaDoc");
							List<RecolectaDocDTO> lista = oQuery
									.setResultTransformer(Transformers
											.aliasToBean(RecolectaDoc.class)).list();
							return lista;
						}
					});
//			return jdbcTemplate.query("SELECT idRecolectaDoc, idCliente, idDireccion, contacto, tel1, ext1, periodoRec, horarioRecoleccion, zona FROM " + getTableName() + " ORDER BY idRecolectaDoc", this);
		}
		catch (Exception e) {
			throw new DaoException("Query failed", e);
		}
		return recolecta;
	}

	/** 
	 * Returns all rows from the RECOLECTA_DOC table that match the criteria 'idRecolectaDoc = :idRecolectaDoc'.
	 */
	@Override
	@Transactional
	public List<RecolectaDocDTO> findWhereIdRecolectaDocEquals(final int idRecolectaDoc) throws DaoException
	{
		List<RecolectaDocDTO> list = new ArrayList<RecolectaDocDTO>();
		RecolectaDocDTO recolecta = this.findByPrimaryKey(idRecolectaDoc);
		list.add(recolecta);
		return list;
	}

	/** 
	 * Returns all rows from the RECOLECTA_DOC table that match the criteria 'idCliente = :idCliente'.
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional
	public List<RecolectaDocDTO> findWhereIdClienteEquals( final int idCliente) 
			throws DaoException	{
		List<RecolectaDocDTO> recolecta = null;
		try {
			recolecta = (List<RecolectaDocDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
					
							Query oQuery = session.createQuery("SELECT rec.idRecolectaDoc as idRecolectaDoc, rec.idCliente as idCliente," +
									" rec.idDireccion as idDireccion, rec.contacto as contacto, rec.tel1 as tel1, rec.ext1 as ext1, rec.periodoRec as periodoRec," +
									" rec.horarioRecoleccion as horarioRecoleccion, rec.zona as zona, rec.lugarRecoleccion as lugarRecoleccion FROM " + getTableName() + 
									" rec WHERE idCliente = :cliente ORDER BY rec.idCliente");
							oQuery.setInteger("cliente", idCliente);
							List<RecolectaDocDTO> recolectaDto = oQuery.setResultTransformer(Transformers.aliasToBean(
									RecolectaDocDTO.class)).list();
							return recolectaDto;
						}
					});
//			return jdbcTemplate.query("SELECT idRecolectaDoc, idCliente, idDireccion, contacto, tel1, ext1, periodoRec, horarioRecoleccion, zona FROM " + getTableName() + " WHERE idCliente = ? ORDER BY idCliente", this,idCliente);
		}
		catch (Exception e) {
			throw new DaoException("Query failed", e);
		}
		return recolecta;
	}

	/** 
	 * Returns all rows from the RECOLECTA_DOC table that match the criteria 'idDireccion = :idDireccion'.
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional
	public List<RecolectaDocDTO> findWhereIdDireccionEquals(final int idDireccion)
			throws DaoException {
		List<RecolectaDocDTO> recolecta = null;
		try {
			recolecta = (List<RecolectaDocDTO>)getHibernateTemplate().execute(
					new HibernateCallback() {
						
						@Override
						public Object doInHibernate(Session session) throws HibernateException,
								SQLException {
						
							Query oQuery = session.createQuery("SELECT rec.idRecolectaDoc as idRecolectaDoc, rec.idCliente as idCliente," +
									" rec.idDireccion as idDireccion, rec.contacto as contacto, rec.tel1 as tel1, rec.ext1 as ext1, rec.periodoRec as periodoRec," +
									" rec.horarioRecoleccion as horarioRecoleccion, rec.zona as zona rec.lugarRecoleccion as lugarRecoleccion  FROM " + getTableName() + 
									" rec WHERE idCliente = :cliente ORDER BY rec.idCliente");
							oQuery.setInteger(0, idDireccion);
							List<RecolectaDocDTO> recolectaDto = oQuery.setResultTransformer(Transformers.aliasToBean(
									RecolectaDocDTO.class)).list();
							return recolectaDto;
						}
					});
//			return jdbcTemplate.query("SELECT idRecolectaDoc, idCliente, idDireccion, contacto, tel1, ext1, periodoRec, horarioRecoleccion, zona FROM " + getTableName() + " WHERE idDireccion = ? ORDER BY idDireccion", this,idDireccion);
		}
		catch (Exception e) {
			throw new DaoException("Query failed", e);
		}
		return recolecta;
	}

	/** 
	 * Returns all rows from the RECOLECTA_DOC table that match the criteria 'contacto = :contacto'.
	 
	@Transactional
	public List<RecolectaDocDTO> findWhereContactoEquals(String contacto) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idRecolectaDoc, idCliente, idDireccion, contacto, tel1, ext1, periodoRec, horarioRecoleccion, zona FROM " + getTableName() + " WHERE contacto = ? ORDER BY contacto", this,contacto);
		}
		catch (Exception e) {
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the RECOLECTA_DOC table that match the criteria 'tel1 = :tel1'.
	 
	@Transactional
	public List<RecolectaDocDTO> findWhereTel1Equals(int tel1) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idRecolectaDoc, idCliente, idDireccion, contacto, tel1, ext1, periodoRec, horarioRecoleccion, zona FROM " + getTableName() + " WHERE tel1 = ? ORDER BY tel1", this,tel1);
		}
		catch (Exception e) {
			throw new RecolectaDocDaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the RECOLECTA_DOC table that match the criteria 'ext1 = :ext1'.
	 
	@Transactional
	public List<RecolectaDocDTO> findWhereExt1Equals(int ext1) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idRecolectaDoc, idCliente, idDireccion, contacto, tel1, ext1, periodoRec, horarioRecoleccion, zona FROM " + getTableName() + " WHERE ext1 = ? ORDER BY ext1", this,ext1);
		}
		catch (Exception e) {
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the RECOLECTA_DOC table that match the criteria 'periodoRec = :periodoRec'.
	 
	@Transactional
	public List<RecolectaDocDTO> findWherePeriodoRecEquals(String periodoRec) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idRecolectaDoc, idCliente, idDireccion, contacto, tel1, ext1, periodoRec, horarioRecoleccion, zona FROM " + getTableName() + " WHERE periodoRec = ? ORDER BY periodoRec", this,periodoRec);
		}
		catch (Exception e) {
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the RECOLECTA_DOC table that match the criteria 'horarioRecoleccion = :horarioRecoleccion'.
	 
	@Transactional
	public List<RecolectaDocDTO> findWhereHorarioRecoleccionEquals(String horarioRecoleccion) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idRecolectaDoc, idCliente, idDireccion, contacto, tel1, ext1, periodoRec, horarioRecoleccion, zona FROM " + getTableName() + " WHERE horarioRecoleccion = ? ORDER BY horarioRecoleccion", this,horarioRecoleccion);
		}
		catch (Exception e) {
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns all rows from the RECOLECTA_DOC table that match the criteria 'zona = :zona'.
	 
	@Transactional
	public List<RecolectaDocDTO> findWhereZonaEquals(String zona) throws DaoException
	{
		try {
			return jdbcTemplate.query("SELECT idRecolectaDoc, idCliente, idDireccion, contacto, tel1, ext1, periodoRec, horarioRecoleccion, zona FROM " + getTableName() + " WHERE zona = ? ORDER BY zona", this,zona);
		}
		catch (Exception e) {
			throw new DaoException("Query failed", e);
		}
		
	}*/

	/** 
	 * Returns the rows from the RECOLECTA_DOC table that matches the specified primary-key value.
	 
	public RecolectaDocDTO findByPrimaryKey(RecolectaDocPkDTO pk) throws DaoException
	{
		return findByPrimaryKey( pk.getIdRecolectaDoc() );
	}*/
	
	@Override
	public RecolectaDocPkDTO findLastInsert() throws DaoException {
		RecolectaDocPkDTO recolectaDoc = null;
		try {
			recolectaDoc = (RecolectaDocPkDTO) getHibernateTemplate().execute(
					new HibernateCallback() {

						@Override
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							
							Query oQuery = session
									.createQuery("SELECT max(r.idRecolectaDoc) as idRecolectaDoc FROM "
											+ getTableName()
											+ " r ");
							
							RecolectaDocPkDTO recolectaDocDto = (RecolectaDocPkDTO) oQuery
									.setResultTransformer(Transformers
											.aliasToBean(RecolectaDocPkDTO.class)).list().get(0);
							return recolectaDocDto;
						}
					});
		
		} catch (Exception e) {
			logger.error("Error: RecolectaDocDaoImpl, cause: " + e.getMessage(), e);
			throw new DaoException("Query failed", e);
		}
		return recolectaDoc;

	}

	/* (non-Javadoc)
	 * @see com.sophossystems.client.asmon.dao.RecolectaDocDao#findByPrimaryKey(com.sophossystems.client.asmon.dto.RecolectaDocPkDTO)
	 */
	@Override
	public RecolectaDocDTO findByPrimaryKey(RecolectaDocPkDTO pk)
			throws DaoException {
		this.findByPrimaryKey(pk.getIdRecolectaDoc());
		return null;
	}


}
